Excel Reference Manual Home ExcelVBAPartsCollection DownLoad My Profile
Menu Back Next Links Excel Function Manual Myself My BBS

串刺し計算(統合)

串刺し計算(統合)の機能と種類
コピー後、加算して貼り付け
3D参照 (計算式) による統合
同一形式リストの統合
見出しが異なる場合の統合



1 串刺し計算(統合)の種類

1 串刺し計算(統合)の機能

 たとえば、各支店の品名別売上を表す複数のワークシートがある場合、各支店の品名別売上を一つのワークシートに集計する処理を一般に「串刺し計算」計算と呼んでいます。Excelではこの処理を「統合」といいます。
 下表は、(L1)〜(L3)のワークシートの各支店データを(L4)の支店合計シートに統合によりした結果です。


2 統合の種類

 データの統合を行う方法には、リストの形式・ワークシート上のリストの配置場所などによっていくつかの種類があります。
 
(1) 「コピー」・「形式を選択して貼り付け」による加算
 各シートのデータをコピーして、「形式を選択して貼り付け」の演算機能の「加算」により集計シートに貼り付けて集計する方法です。(詳細はこちら)
 
(2) 3D参照(数式)による統合
 複数のワークシートを参照する数式のことを「3D参照」といいます。3D参照の数式は次のように表されます。「=SUM(統合元1!B3,統合元2!B3,統合元3!B3)」。これは、「統合元1」〜「統合元3」のそれぞれのセルB3を合計する計算式を表しています。(詳細はこちら)
 統合元範囲のデータ(加算対象となるデータ)の並び方や配置がワークシートによって異なるような場合は3D参照(数式)による統合を利用します。
 
(3) メニューの「データ(D)」「統合(N)」による統合
 Excelのメニューの「データ(D)」「統合(N)」を利用して統合を行う方法で、データの状態によって「位置による統合」と「項目による統合」があります。
 
  「位置による統合」は、統合元範囲のデータがすべてワークシートの同じ位置、同じ順序で並んでいる場合に利用します。(詳細はこちら)
 
 「項目による統合」は、統合元範囲のデータの並び方や配置が多少異なっているが、行見出しや列見出しが同じ複数のワークシートを集計する場合に利用します。(詳細はこちら)
 
 どちらの方法も元データが変更されると、自動的に統合結果が更新されるように設定することができますが、統合に含まれるセルや範囲を変更することはできません。また、手動で統合を更新することもでき、この場合は、統合に含まれるセルや範囲を変更することができます。





2 「コピー」・「形式を選択して貼り付け」による加算(統合)

 累計計算や串刺し計算(統合)で最も簡単な方法に、データをコピーして、メニューの「形式を選択して貼り付け」を利用する方法があります。
 
1. 串刺し計算をするシート(統合元シート)のデータ領域を選択します。
2. 選択範囲内で右クリックして、ショートカットメニューの「コピー(C)」を選択します。
3. 集計用のリスト(統合先シート)のデータ部分の左上端となるセルを右クリックして、ショートカットメニューの「形式を選択して貼り付け(S)」を選択します。
4. 「形式を選択して貼り付け」ダイアログボックスの貼り付け欄で「値(V)」のオプションボタンと演算欄で「加算(D)」のオプションボタンをオンにします。
5. 「OK」ボタンをクリックします。
6. 上記の1〜5を統合するワークシートの数分繰返します。2回目以降のコピー、貼り付けを行うと前回のコピー結果に貼り付けたデータが加算されます。





3 3D参照 (計算式) による統合

 毎月のデータを年間集計表にまとめるように、統合元範囲のデータがすべて同じレイアウトで作成されている場合は、シート名を使って3D参照による串刺し計算を行うことができます。
 この場合、月報に相当するシートを「参照元シート」、年間集計表に相当するシートを「参照先シート」と言います。

1 参照元シートが連続している場合の統合

 計算対象となるワークシートが、同一ブック内に連続して並んでいる場合の計算方法です。
 
1. 参照先シート見出しタブをクリックします。
2. 合計を算出する領域の左上端セル(B3)を選択して「オートSUM」ボタンをクリックすると、セルに「=SUM()」と計算式が表示されます 。(下図)

「オートSUM」ボタン

3. 計算対象の最初の参照元シート見出しタブをクリックします。(下左図)
4. 計算対象シートの終わりの参照元シート見出しタブを「Shift」キーを押しながらクリックすると、最初にクリックしたシートから終わりの参照シートまでが選択されます。(下右図)
(画面に表示されているシートは、最初の参照元シートのままになっている)

 

5. 表示されているシート(最初の参照シート)で、合計を算出する領域の左上端セル(B3セル)をクリックしてから「Enter」キーを押すと、参照先シートのB3セルに合計が表示されます。(下左図)
 なお、セルB3をクリックすると、数式バーで入力された計算式(下右図)を見ることが出来ます。数式の意味は、シート「統合元1」からシート「統合元3」までのセルB3を合計する、を表しています。

6. セルB3をク選択し、右下隅のフィルハンドルを下にドラッグしてデータ範囲の下端まで計算式を複写します。
7. 上記でコピーした範囲を選択し、選択範囲右下隅セルのフィルハンドルを右にドラッグしてデータ範囲の右端まで計算式を複写します。下図は計算式複写後の参照先シートの内容です。


2 参照元シートが並んでいない場合の統合

 計算対象となる参照先シートが、連続して並んでいない場合の計算方法です。図は上記とほぼ同様ですので省略します。
 
1. 参照先シート見出しタブをクリックします。
2. 合計を算出する領域の左上端セル(B3)を選択して「オートSUM」ボタンをクリックすると、セルに「=SUM()」と計算式が表示されます 。
3. 計算対象の最初の参照元シート見出しタブをクリックします。
4. 参照先シートの計算式入力セルと同じ位置のセル(B3)をクリックすると、数式バーに最初の参照元シート名とセル番地が表示されますので、右括弧の前に「 , (カンマ)」を入力します。
 なお、最後の参照元シートのセルをクリックしたときは、次に「 , (カンマ)」を入力しないでください。
5. 次の参照元シート見出しをクリックして、上記 4 と同様の処理を行い、この処理を計算する参照元シート回数分繰返します。
6. 最後の参照元シートのセルをクリックしたら「Enter」キーを押すと、参照先シートに計算式が入力されます。
7. セルB3をク選択し、右下隅のフィルハンドルを下にドラッグしてデータ範囲の下端まで計算式を複写します。
8. 上記でコピーした範囲を選択し、選択範囲右下隅セルのフィルハンドルを右にドラッグしてデータ範囲の右端まで計算式を複写します。





4 同一形式リストの統合

 統合元のデータがすべて同じ位置、同じ順序で並んでいる同一形式リストの場合は、「位置による統合」を行います。
 位置による統合を行うには、統合元のデータがリスト形式(先頭行には列見だしがあり、各シートの列見だし、行見出しに同じ項目名が入力されている。)になっている必要があります。
 また、統合元範囲の列見出しや行見出しは統合先範囲にコピーされませんので統合先ワークシートに見だしを表示するには、統合元シートのいずれかから見だしを複写するか、入力する必要があります。

 下表は、同じ形式の(L1)〜(L3)ワークシートの各支店データを(L4)の支店合計シートに統合によりした結果です。


1. 統合先範囲の左上隅のセルをクリックします。
 下図では、予め見だしをコピーしてありますので、データ部分の左上端セル(B3)をクリックします。


2. メニューの「データ(D)」「統合(N)」を選択します。
3. 「統合の設定」ダイアログボックス(下図)の[集計の方法(F)] ボックスの統合に使う集計方法をクリックします。集計方法は、「合計、データの個数、平均、最大値、最小値、積、数値の個数、標本標準偏差、標準偏差、標本分散、分散」の11種類です。


4. 「統合元範囲(R)」ボックスの右端にあるダイアログボックス縮小ボタン(赤い矢印の部分)をクリックするとダイアログボックスが折りたたまれますので、統合元シートの統合するセル範囲を指定します。
 下図では、リストのデータ部分(セルB3:G7)のみの範囲指定を行っています。


5. ダイアログボックス縮小ボタン(赤い矢印の部分)をクリックすると、ダイアログボックスが元の大きさに戻り、「統合元範囲(R)」ボックスに選択範囲が表示されているのを確認して、「追加(A)」ボタンをクリックすると「統合元(E)」ボックスに指定結果が追加されます。(下図)


6. すべての統合元シートの範囲について手順 4 〜 5 を繰り返します。下図は、その結果です。


7. 「統合の基準」の「上端行(T)」「左端列(L)」のチェックボックスはオフにしておきます。
 統合元データの変更に応じて統合テーブルが自動的に更新されるようにするには、「統合元データとリンクする」チェック ボックスをオンにします。いったんリンクを設定すると、統合されたデータを含む統合元範囲に新規に追加したり、変更を加えたりすることはできません。
下図は、データの統合結果です。






5 見出しが異なる場合の統合

 統合元範囲のデータの並び方や配置は異なっていても、行見出しや列見出しが同じ複数のワークシートを集計する場合は、「項目による統合」を行います。
 項目による統合を行うには、統合元のデータがリスト形式(先頭行には列見だしがあり、各シートの列見だし、行見出しに同じ項目名が入力されている。)になっている必要があります。
 また、統合の結果、統合元範囲の列見出しや行見出しも統合先範囲にコピーされます。
 
 下表は、同じ形式の(L1)〜(L3)ワークシートの各支店データを(L4)の支店合計シートに統合します。


1. 統合先範囲の左上隅のセル(セルA2)をクリックします。
 

2. メニューの「データ(D)」「統合(N)」を選択します。
3. 「統合の設定」ダイアログボックス(下図)の[集計の方法(F)] ボックスの統合に使う集計方法をクリックします。集計方法は、「合計、データの個数、平均、最大値、最小値、積、数値の個数、標本標準偏差、標準偏差、標本分散、分散」の11種類です。


4. 「統合元範囲(R)」ボックスの右端にあるダイアログボックス縮小ボタン(赤い矢印の部分)をクリックするとダイアログボックスが折りたたまれますので、統合元シートの統合するセル範囲を指定します。
 下図では、リスト範囲全体(セルA2:G7)の範囲指定を行っています。


5. ダイアログボックス縮小ボタン(赤い矢印の部分)をクリックすると、ダイアログボックスが元の大きさに戻り、「統合元範囲(R)」ボックスに選択範囲が表示されているのを確認して、「追加(A)」ボタンをクリックすると「統合元(E)」ボックスに指定結果が追加されます。(下図)


6. すべての統合元シートの範囲について手順 4 〜 5 を繰り返します。下図は、その結果です。


7. 「統合の基準」の「上端行(T)」「左端列(L)」のチェックボックスをオンにします。
 統合元データの変更に応じて統合テーブルが自動的に更新されるようにするには、「統合元データとリンクする」チェック ボックスをオンにします。いったんリンクを設定すると、統合されたデータを含む統合元範囲に新規に追加したり、変更を加えたりすることはできません。
下図は、データの統合結果です。






PC用眼鏡【管理人も使ってますがマジで疲れません】 解約手数料0円【あしたでんき】 Yahoo 楽天 NTT-X Store

無料ホームページ 無料のクレジットカード 海外格安航空券 ふるさと納税 海外旅行保険が無料! 海外ホテル